package com.example.mapper;

import java.util.List;

import com.example.dto.ClassStudentDTO;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.entity.ClassStudent;
import com.example.entity.User;

@Mapper
public interface ClassStudentMapper extends BaseMapper<ClassStudent> {
    
    @Select("SELECT cs.* FROM class_students cs " +
            "WHERE cs.class_id = #{classId} AND cs.status = 'active'")
    List<ClassStudent> findByClassId(@Param("classId") Long classId);
    
    @Select("SELECT cs.* FROM class_students cs " +
            "WHERE cs.student_id = #{studentId} AND cs.status = 'active'")
    List<ClassStudent> findByStudentId(@Param("studentId") Long studentId);
    
    @Select("SELECT cs.* FROM class_students cs " +
            "WHERE cs.class_id = #{classId} AND cs.student_id = #{studentId}")
    ClassStudent findByClassIdAndStudentId(@Param("classId") Long classId, @Param("studentId") Long studentId);
    
    @Delete("DELETE FROM class_students WHERE class_id = #{classId} AND student_id = #{studentId}")
    int deleteByClassIdAndStudentId(@Param("classId") Long classId, @Param("studentId") Long studentId);

    @Select("<script>" +
            "SELECT u.*, cs.join_date FROM users u " +
            "JOIN class_students cs ON u.id = cs.student_id " +
            "WHERE cs.class_id = #{classId} AND cs.status = 'active' " +
            "<if test='query != null and query != \"\"'>" +
            "  AND (u.username LIKE CONCAT('%', #{query}, '%') " +
            "       OR u.full_name LIKE CONCAT('%', #{query}, '%') " +
            "       OR u.email LIKE CONCAT('%', #{query}, '%') " +
            "       OR u.exam_number LIKE CONCAT('%', #{query}, '%'))" +
            "</if>" +
            "ORDER BY cs.join_date DESC" +
            "</script>")
    Page<ClassStudentDTO> findStudentsInClass(Page<ClassStudentDTO> page, @Param("classId") Long classId, @Param("query") String query);


    @Select("SELECT COUNT(*) FROM class_students WHERE class_id = #{classId} AND status = 'active'")
    int countStudentsInClass(@Param("classId") Long classId);
} 